"""
Case Type   : 功能测试
Case Name   : 两个自治事物竞争行锁
Create At   : 2024/7/3
Owner       : peilinqian
Description :
    step1: 创建表;
    step2: 创建两个函数，函数内事务互相存在锁;
    step3: 开始执行函数1;
    step4: 执行函数1的过程中执行函数2;
    step5: 函数1、函数2执行结果验证;
Expect      :
    step1: 创建表; expect：创建成功
    step2: 创建两个函数，函数内事务互相存在锁;expect:创建成功
    step3: 开始执行函数1; expect:调用成功
    step4: 执行函数1的过程中执行函数2; expect:调用成功，执行时间大于10s以上
    step5: 函数1、函数2执行结果验证; expect:表更新结果正确
History     :
"""
import os
import time
import unittest

from testcase.utils.ComThread import ComThread
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class DblinkCase(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)}:初始化----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.com = Common()
        self.constant = Constant()
        self.t_name = 't_autonomous_transaction_0051'
        self.func_name1 = 'f_autonomous_transaction_0051_1'
        self.func_name2 = 'f_autonomous_transaction_0051_2'

    def test_main(self):
        step_txt = '----step1: 创建表; expect：创建成功----'
        self.log.info(step_txt)
        sql = f"drop table if exists {self.t_name};" \
              f"create table {self.t_name} (id int,t1 date,t2 date);" \
              f"insert into {self.t_name} values (10,sysdate,sysdate)," \
              f"(11,sysdate,sysdate);"
        msg = self.pri_sh.execut_db_sql(sql)
        self.log.info(msg)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, msg,
                      '执行失败:' + step_txt)

        step_txt = '----step2: 创建两个函数，函数内事务互相存在锁;' \
                   'expect:创建成功----; '
        self.log.info(step_txt)
        sql = f'''CREATE OR REPLACE FUNCTION {self.func_name1}()
        RETURNS date AS \$\$
        DECLARE
            pragma autonomous_transaction;
            result_record date;
        BEGIN
            update {self.t_name} set t1=sysdate where id=10;
            perform pg_sleep(15);
            SELECT t1 INTO result_record FROM {self.t_name} WHERE  id=10;
            RETURN result_record;
        END;
        \$\$ LANGUAGE plpgsql;
        CREATE OR REPLACE FUNCTION {self.func_name2}()
        RETURNS date AS \$\$
        DECLARE
            pragma autonomous_transaction;
            result_record date;
        BEGIN
            update {self.t_name} set t1=sysdate where id=10;
            SELECT t1 INTO result_record FROM {self.t_name} WHERE id=10;
            RETURN result_record;
        END;
        \$\$ LANGUAGE plpgsql;
        '''
        msg = self.pri_sh.execut_db_sql(sql)
        self.log.info(msg)
        self.assertIn(self.constant.CREATE_FUNCTION_SUCCESS_MSG, msg,
                      '执行失败:' + step_txt)

        step3_txt = '----step3: 开始执行函数1; expect:调用成功----; '
        self.log.info(step3_txt)
        sql1 = f"select {self.func_name1}();"
        session1 = ComThread(self.pri_sh.execut_db_sql, args=(sql1,))
        session1.setDaemon(True)
        session1.start()

        time.sleep(2)
        step_txt = '----step4: 执行函数1的过程中执行函数2; expect:调用成功，执行时间大于10s以上----; '
        self.log.info(step_txt)
        begin_time = time.time()
        sql2 = f"select sysdate; select {self.func_name2}(); select sysdate;"
        msg2 = self.pri_sh.execut_db_sql(sql2)
        self.log.info(msg2)
        end_time = time.time()
        delta_time = end_time - begin_time
        self.log.info(delta_time)
        self.assertGreater(delta_time, 10, '执行失败:' + step_txt)

        self.log.info('----获取函数1执行结果----')
        session1.join()
        msg1 = session1.get_result()
        self.log.info(msg1)
        self.assertIn('1 row', msg1, '执行失败:' + step3_txt)

        step_txt = '----step5: 函数1、函数2执行结果验证; expect:表更新结果正确----; '
        self.log.info(step_txt)
        sql2 = f"select t1 from {self.t_name} WHERE id=10;"
        msg3 = self.pri_sh.execut_db_sql(sql2).splitlines()[-2].strip()
        self.assertIn(msg3, msg2, '执行失败:' + step3_txt)

    def tearDown(self):
        self.log.info('----this is tearDown----')
        step_txt = '----删除表，恢复参数; expect：成功----'
        self.log.info(step_txt)
        sql = f"drop table if exists {self.t_name};"
        msg1 = self.pri_sh.execut_db_sql(sql)
        self.log.info(msg1)
        sql = f"drop function {self.func_name1};" \
              f"drop function {self.func_name2};"
        msg2 = self.pri_sh.execut_db_sql(sql)
        self.log.info(msg2)

        self.log.info(f'----{os.path.basename(__file__)}:执行完毕----')
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, msg1,
                      '执行失败:' + step_txt)
        self.assertIn(self.constant.DROP_FUNCTION_SUCCESS_MSG, msg2,
                      '执行失败:' + step_txt)
